'''
An RPG Modification for Source games using Source Python.

    Copyright (C) 2012 Steven Hartin

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.

Created on 22 Dec 2012

@author: Steven Hartin
@file: ../sourcerpg/database/api/sqlite/database.py
'''

from sqlite3 import dbapi2 as sqlite
from time import time

from players.helpers import index_from_playerinfo, index_from_uniqueid
from players.entity import PlayerEntity

from sourcerpg.database.basedatabase import BaseDatabase
from sourcerpg.database.cache import CacheProperty
from sourcerpg.logging import logger, loglevels


class Database(BaseDatabase):
    """SQLite API for the database implementation"""
    def __init__(self, path=":memory:", parser_path=None, meta_path=None):
        """Open the database and initialise the connection / cursor objects

        @param str path The path to the database
        @param str parser_path The path to the parsed information
        @param str meta_path The path to the meta information

        """
        self.connection = sqlite.connect(path)
        logger.log("SQLite path: %s successfully connected" % path,
            level=loglevels.INFO_LEVEL_1)
        # Use the row object to return the row data
        self.connection.row_factory = sqlite.Row
        self.cursor = self.connection.cursor()

        self.cursor.execute("PRAGMA journal_mode=PERSIST")
        self.cursor.execute("PRAGMA locking_mode=EXCLUSIVE")
        self.cursor.execute("PRAGMA synchronous=OFF")
        # 10 MB journal limit
        self.cursor.execute("PRAGMA journal_size_limit=10485760")

        super(Database, self).__init__(parser_path, meta_path)

    def __del__(self):
        """Ensure the cursor and connection are not left open"""
        logger.log("Closing SQLite connection", level=loglevels.INFO_LEVEL_1)
        self.cursor.close()
        self.connection.close()
        self.cursor = None
        self.connection = None

    def execute_raw_command(self, command, *args):
        """Execute a raw SQL command on the cursor

        @param str command The raw SQL command to execute
        @param tuple args A list of arguments to pass into command

        """
        logger.log("Executing raw command: %s" % command,
            level=loglevels.INFO_LEVEL_2)
        logger.log("Params: %s" % ",".join(map(str, args)),
            level=loglevels.INFO_LEVEL_2)
        self.cursor.execute(command, args)

    def update_player_cache(self, player):
        """Updates the cache for a local player to the databases' values.

        @param PlayerEntity player the player to update from the database

        """
        assert type(player) == PlayerEntity

        self.cursor.execute("SELECT id, xp, credits, level FROM players WHERE"
            " steamid=?", (player.uniqueid,))
        result_row = self.cursor.fetchone()
        if result_row is not None:
            player_cache = self["players"][player.uniqueid]
            player_cache.id = result_row["id"]
            player_cache["xp"] = result_row["xp"]
            player_cache["level"] = result_row["level"]
            player_cache["credits"] = result_row["credits"]
            self.cursor.execute("SELECT id, name, level FROM skills "
                "WHERE player_id=?", (result_row["id"],))
            for result_row in self.cursor.fetchall():
                # Find all the stored skills and restore
                name = result_row["name"]
                level = result_row["level"]
                player_cache["skills"][name] = {}
                skill_cache = player_cache["skills"][name]
                skill_cache["level"] = level
                skill_cache.id = result_row["id"]

    def commit_cache(self):
        """Commits any dirty / new cache objects to the database

        Any new player / skill or any player / skill which have changed in the
        cache since the last commit needs to be reflected in the database. This
        should contain all the inserts and updates to the database transactions
        and then finally save the database.

        """
        commit = False
        logger.log("Committing SQLite Cache", level=loglevels.INFO_LEVEL_2)
        for steamid, player in self["players"].items():
            # Update the last connected / name of the player
            logger.log("Committing Player %s" % player["name"],
                level=loglevels.INFO_LEVEL_3)
            if player.is_dirty(True):
                # Either the player or its children have had their data altered
                # since the last commit
                logger.log("Player is dirty, updating",
                    level=loglevels.INFO_LEVEL_4)
                # Direct player data has altered
                values = {}
                for key, value in player.items():
                    if isinstance(value, CacheProperty):
                        if value.is_dirty():
                            values[key] = value.value
                self.execute_raw_command(
                    "UPDATE players SET %s WHERE id=?" % (
                    ",".join(map(lambda k: "%s=?" % k, values.keys()))),
                    *(tuple(values.values()) + tuple((player.id,))))
                for skill_name, skill in player["skills"].items():
                    if skill.is_new():
                        # This skill's level has altered
                        level = skill["level"]
                        self.execute_raw_command(
                            "INSERT INTO skills (level, name, "
                            "player_id) VALUES (?,?,?)", level, skill_name,
                            player.id)
                        skill.id = self.cursor.lastrowid
                    elif skill.is_dirty():
                        level = skill["level"]
                        self.execute_raw_command(
                            "UPDATE skills SET level=? WHERE "
                            "id=?", level, skill.id)
                # We've updated at least 1 row, commit the database
                commit = True
            if player.is_new(True):
                # Some information about the player / children have been
                # created
                logger.log("Player is new, inserting",
                    level=loglevels.INFO_LEVEL_3)
                if player.is_new():
                    # The player itself has been created, new player entry
                    values = {}
                    for key, value in player.items():
                        if isinstance(value, CacheProperty):
                            values[key] = value.value
                    values["steamid"] = steamid
                    self.execute_raw_command(
                        "INSERT INTO players (%s) VALUES (%s)" % (
                        ",".join(values.keys()),
                        ",".join(tuple("?" * len(values)))),
                        *tuple(values.values()))
                    player.id = self.cursor.lastrowid
                for skill_name, skill in player["skills"].items():
                    if skill.is_new():
                        # This particular skill has been created
                        level = skill["level"]
                        self.execute_raw_command(
                            "INSERT INTO skills (level, "
                            "player_id, name) VALUES (?,?,?)", level,
                            player.id, skill_name)
                        skill.id = self.cursor.lastrowid
                # We've inserted at least 1 row, commit the database
                commit = True
        # Finally force all the dirty flags to False to make sure we reflect
        # the commit to the cache
        self.set_dirty(False)

        if commit is True:
            # Commit the database
            self.connection.commit()

    def delete_inactive_players_api(self, inactive_timer):
        # (Months, Weeks, Days, Hours)
        command = ("DELETE FROM skills WHERE id=("
            "SELECT id FROM players WHERE lastconnected < "
            "(SELECT strftime('%s', 'now')) - %d)" % inactive_timer)
        self.execute_raw_command(command)
        command = ("DELETE FROM players WHERE id=("
            "SELECT id FROM players WHERE lastconnected < "
            "(SELECT strftime('%s', 'now')) - %d)" % inactive_timer)
        self.execute_raw_command(command)
